package cn.jdy.lhkgaj.service;
import java.math.BigInteger;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.hibernate.transform.Transformers;
import org.springframework.stereotype.Service;

import cn.jdy.framework.db.hibernate.BaseDao;
import cn.jdy.framework.log.BaseLog;
import cn.jdy.lhkgaj.entity.LhkgajNews;
import cn.jdy.platform.entity.ConfValue;
/**
 * 排名操作类
 * @author LuTan
 *
 */
@Service
public class LhkgajRankingService extends BaseDao<LhkgajNews> {
	/**
	 * 投稿排名（局直）3
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public List<Map<String, String>> querySiteMap() {
		String sql = "select value0,count(a.id) sum from (select b.value0,b.value3 from confvalue b where  b.sort<25 and b.typeid=72) aaa LEFT JOIN lhkgajnews a on a.username=aaa.value3 GROUP BY value0 ORDER BY sum desc LIMIT 3 ";
		Query sqlQuery = super.em().createSQLQuery(sql);
		// 将查询寻结果转换为Map格式，解决多表查询或零散字段查询 映射取值问题
		sqlQuery.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		return sqlQuery.list();
	}
	/**
	 * 投稿排名（局直）4
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public List<Map<String, String>> querySiteMap01() {
		String sql = "select value0,count(a.id) sum from (select b.value0,b.value3 from confvalue b where  b.sort<25 and b.typeid=72) aaa LEFT JOIN lhkgajnews a on a.username=aaa.value3 GROUP BY value0 ORDER BY sum desc ";
		Query sqlQuery = super.em().createSQLQuery(sql);
		// 将查询寻结果转换为Map格式，解决多表查询或零散字段查询 映射取值问题
		sqlQuery.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		return sqlQuery.list();
	}
	/**
	 * 投稿排名（派出所）3
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public List<Map<String, String>> querySiteMap1() {
		String sql = "select value0,count(a.id) sum from (select b.value0,b.value3 from confvalue b where  b.sort>24 and b.typeid=72) aaa LEFT JOIN lhkgajnews a on a.username=aaa.value3 GROUP BY value0 ORDER BY sum desc LIMIT 3 ";
		Query sqlQuery = super.em().createSQLQuery(sql);
		// 将查询寻结果转换为Map格式，解决多表查询或零散字段查询 映射取值问题
		sqlQuery.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		return sqlQuery.list();
	}
	/**
	 * 投稿排名（派出所）4
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public List<Map<String, String>> querySiteMap11() {
		String sql = "select value0,count(a.id) sum from (select b.value0,b.value3 from confvalue b where  b.sort>24 and b.typeid=72) aaa LEFT JOIN lhkgajnews a on a.username=aaa.value3 GROUP BY value0 ORDER BY sum desc  ";
		Query sqlQuery = super.em().createSQLQuery(sql);
		// 将查询寻结果转换为Map格式，解决多表查询或零散字段查询 映射取值问题
		sqlQuery.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		return sqlQuery.list();
	}
	
	/**
	 * 浏览排名(局直)3
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public List<Map<String, String>> queryremarkMap() {
		String sql = "select value0 ,value2 from confvalue WHERE typeid=72 and sort<25 order by cast(value2 as SIGNED INTEGER) DESC LIMIT 3 ";
		Query sqlQuery = super.em().createSQLQuery(sql);
		// 将查询寻结果转换为Map格式，解决多表查询或零散字段查询 映射取值问题
		sqlQuery.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		return sqlQuery.list();
	}
	/**
	 * 浏览排名(局直)4
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public List<Map<String, String>> queryremarkMap01() {
		String sql = "select value0 ,value2 from confvalue WHERE typeid=72 and sort<25 order by cast(value2 as SIGNED INTEGER) DESC  ";
		Query sqlQuery = super.em().createSQLQuery(sql);
		// 将查询寻结果转换为Map格式，解决多表查询或零散字段查询 映射取值问题
		sqlQuery.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		return sqlQuery.list();
	}
	/**
	 * 浏览排名(派出所)前3
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public List<Map<String, String>> queryremarkMap1() {
		String sql = "select value0 ,value2 from confvalue WHERE typeid=72 and sort>24 order by cast(value2 as SIGNED INTEGER) DESC LIMIT 3 ";
		Query sqlQuery = super.em().createSQLQuery(sql);
		// 将查询寻结果转换为Map格式，解决多表查询或零散字段查询 映射取值问题
		sqlQuery.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		return sqlQuery.list();
	}
	/**
	 * 浏览排名(局直)前4
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public List<Map<String, String>> queryremarkMap11() {
		String sql = "select value0 ,value2 from confvalue WHERE typeid=72 and sort>24 order by cast(value2 as SIGNED INTEGER) DESC  ";
		Query sqlQuery = super.em().createSQLQuery(sql);
		// 将查询寻结果转换为Map格式，解决多表查询或零散字段查询 映射取值问题
		sqlQuery.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		return sqlQuery.list();
	}
	
	public void updateremark(Long id) {
			String sql = "UPDATE confvalue SET value2=value2+1 WHERE id =:id and typeid=72";
			Query query=super.em().createSQLQuery(sql);
			query.setParameter("id",id);
			query.executeUpdate();
	}
	
	public List<ConfValue> getremarkip() {
		List<ConfValue> list = new ArrayList<ConfValue>();
		try {
			String sql = "SELECT * FROM confvalue WHERE typeid=72";
			Query query = super.em().createSQLQuery(sql).addEntity(ConfValue.class);;
			list = query.list();
		} catch (Exception e) {
			BaseLog.e(this.getClass(), "查询异常", e);
		}
		return list;
	}


	/**
	 * 查询今日值班最新的一条记录
	 * @param name
	 * @return
	 */
	public Map<String, Object> get_dutyid() {
		Map<String, Object> map = new HashMap<String, Object>();
	   // String sql = "SELECT COUNT(id) FROM associations WHERE "+name+" IS NOT NULL";
		String sql = "select id from lhkgajnews where columnId=10585 ORDER BY falseCreateTime desc limit 1 ";
	    SQLQuery query = super.em().createSQLQuery(sql.toString());
	    map.put("dataList", query.list());
	    return map;
	  }
	
	/**
	   * 查询部门
	   * @return
	   */
	  public long get_id(){
		  Map<String, Object> map=this.get_dutyid();
		  List list=(List)map.get("dataList");//cnt
		  int imax=list.size();
		  for(int i=0;i<imax;i++){
			  Long cnt = ((BigInteger) list.get(i)).longValue();
			  return cnt;
		  }
		  return 0;
	  }
}
